﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Drawing.Text;
using System.Windows;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SpectationClient;
using SpectationClient.SQLCommandBuilder;
using System.IO;
using SpectationClient.Stuff;
//using CSV_ImportDialog;
using SpectationClient.DataBaseDescription;
//using ToolLibrary.Forms;
//using ToolLibrary.BackgroundWorker;
using Npgsql;
using SpectationClient.Async;

namespace SpectationClient.GUI {
    public partial class SubShowTables : Form {
        DBManager dbm = null;
        //PostGIS_Npgsql_CommandBuilder CMB;
        NpgsqlCommand tableCommand = new NpgsqlCommand();
        NpgsqlCommand initialCommand;

        DataTable dt = null;
        AGetDatabaseObject AGDB;

        const String C_TITLE_NOTABLE = "Anzeigen von Datenbanktabellen";
        const String C_TITLE_HASTABLE = "Datenbanktabelle ";
        TableInfo RecentTableInfo = null;
        //Dictionary<String, BLOB_Viewer> BlobViewer = new Dictionary<string, BLOB_Viewer>();
 
        public SubShowTables(ref DBManager dbm) {
            InitializeComponent();
            initSubshowTables(dbm, null, null, null);
        }

        public SubShowTables(ref DBManager dbm, TableInfo ti, Condition condition) {
            InitializeComponent();
            initSubshowTables(dbm, ti, condition, null);
            //prepareCommand();
            toolStripBtShow_Click(null, null);
        }
        public SubShowTables(ref DBManager dbm, TableInfo ti, ConditionList conditionList) {
            InitializeComponent();
            initSubshowTables(dbm, ti, null, conditionList);
            //prepareCommand();
            toolStripBtShow_Click(null, null);
        }
        private void initSubshowTables(DBManager dbm, TableInfo recentTableInfo, Condition condition, ConditionList conditionList) {

            this.Icon = Resources.Icon;
            this.dbm = dbm;
            this.AGDB = new AGetDatabaseObject();
            AGDB.GetDataTableCompleted +=new GetDataTableCompletedEventHandler(AGDB_GetDataTableCompleted);
            this.tsTBMax.Text = Properties.Settings.Default.DEF_ROWLIMIT.ToString();
            this.tsTBOFFSET.Text = Properties.Settings.Default.DEF_ROWOFFSET.ToString();
            
            foreach(String n in dbm.getSchemaNames()) {
                this.tsCBSchema.Items.Add(n);
            }

            if(recentTableInfo != null) {
                this.tsCBSchema.SelectedItem = recentTableInfo.Schema.Name;
                this.tsCBTable.SelectedItem = recentTableInfo.Name;
                this.initialCommand = SQLCommandBuilder.CommandBuilder.getSELECT(recentTableInfo, true);
                this.RecentTableInfo = recentTableInfo;
                if(condition != null && !condition.IsEmpty) {
                    this.initialCommand.CommandText += " WHERE ";
                    SQLCommandBuilder.CommandBuilder.appendCmd(ref this.initialCommand, condition.getCmd());
                } else if(conditionList != null && !conditionList.IsEmpty) {
                    this.initialCommand.CommandText += " WHERE ";
                    SQLCommandBuilder.CommandBuilder.appendCmd(ref this.initialCommand, conditionList.getCmd());
                }
            } else {
              //Todo : default tool strips?
            }

            
            //toolStripBtShow_Click(null, null);
        }

        void AGDB_GetDataTableCompleted(object sender, GetDataTableCompletedEventArgs e) {
            if(e.Cancelled) {
                this.RTB_CMD.Text = "Command was canceled";
                this.dt = null;
            } else {
                if(e.Error != null) {
                    this.dt = null;
                    RTB_CMD.Text = TextHelper.Exception2String(e.Error);
                    
                } else {
                    this.dt = e.DataTable;
                    FormHelper.initDGVColumns(DGV, this.dt, this.RecentTableInfo, null, this.dbm.Connection);

                    if(this.RecentTableInfo != null) {
                        setRelatedTablesMenue();
                        ((DataTable)DGV.DataSource).RowChanged +=new DataRowChangeEventHandler(SubShowTables_RowChanged);
                    
                    }
                }

            }
            this.prog_status.Text = "";
            this.tsBtShow.Enabled = true;
            this.tsBtRowsBack.Enabled = true;
            this.tsBtRowsMore.Enabled = true;
            this.Cursor = Cursors.Default;
            DGV.Enabled = true;
        }

        void SubShowTables_RowChanged(object sender, DataRowChangeEventArgs e) {
            DataTable dt = sender as DataTable;
            tsbUpdate.Enabled = true;
        }

        /// <summary>
        /// Sets the ToolStripComboBox-item with name "name" as the active/visible item
        /// </summary>
        /// <param name="cbx">The ToolStripComboBox</param>
        /// <param name="name">The name of the item to be shown</param>
        private void setCBOX_onItemName(ref ToolStripComboBox cbx, String name){
            foreach (Object o in cbx.Items) {
                if (o.GetType() == typeof(String)) {
                    String os = o.ToString();
                    if (os == name) {
                        cbx.SelectedItem = o;
                        break;
                    }
                }
            }
        }




        private void toolStripBtShow_Click(object sender, EventArgs e) {
            if (tsCBTable.SelectedItem != null && EP.GetError(tsTBMax.Control) == "") {
                prepareCommand();
                TableInfo LastTableInfo = this.RecentTableInfo;
                this.RecentTableInfo = dbm.getTableInfo(tsCBSchema.Text, tsCBTable.Text);
       
                if (this.dt == null || LastTableInfo != this.RecentTableInfo) {
                    foreach (ColumnInfo ci in RecentTableInfo.Values) {
                       
                        /*
                        if (ci.Show == false) {
                            MessageBox.Show(
                                String.Format(
                                "Die Spalte \"{0}\" der Datenbanktabelle \"{1}\".\"{2}\" enthält größere Dateien\n " +
                                "und wird nicht mit angezeigt.\n",
                                ci.Name, RecentTableInfo.SchemaName, RecentTableInfo.TableName), "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }*/
                    }
                }
              
                AGDB.GetDataTableAsync(this.tableCommand, this.tableCommand.CommandText);
                this.prog_status.Text = "Get data table ...";
                this.tsBtShow.Enabled = false;
                this.tsBtRowsBack.Enabled = false;
                this.tsBtRowsMore.Enabled = false;
                this.tsbUpdate.Enabled = false;
                this.Cursor = Cursors.WaitCursor;
                DGV.Enabled = false;
            }
        }


        private void setRelatedTablesMenue(){
            this.tsddBt_FindRelatedTables.DropDownItems.Clear();

            //Verbindung zu tabellen, die die hier verlinkten Werte definieren
           // var cIsFKey = from cS in RecentTableInfo.Values 
           //               where cS.

            
            foreach (ForeignKey f in RecentTableInfo.FKeysForOtherTables) {
                String[] targetNames = f.TargetColumns.Select(p => p.Name).ToArray();
                String[] sourceNames = f.SourceColumns.Select(p => p.Name).ToArray();
                ToolStripItem tsi = this.tsddBt_FindRelatedTables.DropDownItems.Add(
                    String.Format("({0}) in {1}({2})"
                                , TextHelper.combine(sourceNames, ",")
                                , f.TargetTable.SchemaTableName
                                , TextHelper.combine(targetNames,",")));
                tsi.TextImageRelation = TextImageRelation.TextBeforeImage;
                tsi.Tag = f;
                tsi.Click += new EventHandler(tsi_Click);    
            }


            if(this.tsddBt_FindRelatedTables.DropDownItems.Count > 0 &&
            RecentTableInfo.FKeysFromOtherTables.Count > 0) {
                this.tsddBt_FindRelatedTables.DropDownItems.Add(new ToolStripSeparator());
            }
            
            foreach(ForeignKey f in RecentTableInfo.FKeysFromOtherTables) {
                String[] targetNames = f.TargetColumns.Select(p => p.Name).ToArray();
                String[] sourceNames = f.SourceColumns.Select(p => p.Name).ToArray();
              
                ToolStripItem tsi = this.tsddBt_FindRelatedTables.DropDownItems.Add(
                
                        String.Format("({0}) aus {1}({2})"
                                , TextHelper.combine(targetNames, ",")
                                , f.SourceTable.SchemaTableName
                                , TextHelper.combine(sourceNames, ",")));

                tsi.TextImageRelation = TextImageRelation.TextBeforeImage;
                tsi.Tag = f;
                tsi.Click += new EventHandler(tsi_Click);
            }
            
            if(this.tsddBt_FindRelatedTables.DropDownItems.Count == 0) {
                //this.tsddBt_FindRelatedTables.DropDownItems.Add("<ID wird nicht als Fremdschlüssel benutzt>");
                this.tsddBt_FindRelatedTables.Enabled = false;
                
            } else {
                this.tsddBt_FindRelatedTables.Enabled = true;
            }
            
        }


        /// <summary>
        /// Event-Handler for a ToolStripItem to open a new Table-Window by using a WHERE-Constrained SELECT Statement
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tsi_Click(object sender, EventArgs e) {
            ToolStripItem tsi = (ToolStripItem)sender;
            if (tsi.Tag is ForeignKey && RecentTableInfo != null) {
                ForeignKey fkey = (ForeignKey)tsi.Tag;


                TableInfo nextTable;
                List<ColumnInfo> thisTableColumns;
                List<ColumnInfo> nextTableColumns;
                if(fkey.TargetTable == RecentTableInfo) {
                    thisTableColumns = fkey.TargetColumns;
                    nextTableColumns = fkey.SourceColumns;
                    nextTable = fkey.SourceTable;
                }else{
                    thisTableColumns = fkey.SourceColumns;
                    nextTableColumns = fkey.TargetColumns;
                    nextTable = fkey.TargetTable;
                }

                //Keys of interest
                List<object[]> values = new List<object[]>();
                if(DGV.SelectedRows.Count > 0) {
                    foreach(DataGridViewRow r in DGV.SelectedRows) {
                        Object[] o = new object[thisTableColumns.Count];
                        for(int i=0; i < thisTableColumns.Count; i++) {
                            o[i] = r.Cells[thisTableColumns[i].Name].Value;
                        }
                        values.Add(o);
                    }
                } else { 
                    foreach(DataGridViewRow r in DGV.Rows) {
                        Object[] o = new object[thisTableColumns.Count];
                        for(int i=0; i < thisTableColumns.Count; i++){
                            o[i] = r.Cells[thisTableColumns[i].Name].Value;
                        }
                        values.Add(o);
                    }
                }
                values = values.Distinct().ToList();

                //Build new command

                NpgsqlCommand cmd = null;
                bool multiColumnID = fkey.TargetColumns.Count > 1;
                if(multiColumnID) {

                    //Case 1: ID has more than one column

                    ConditionList cList = new ConditionList(ConditionList.Op.OR);
                    foreach(Object[] possibleKeyValues in values) {
                        ConditionList cl = new ConditionList(ConditionList.Op.AND);
                        for(int i=0; i < possibleKeyValues.Count(); i++) {
                            cl.Add(new Condition(nextTableColumns[i].Name, Condition.Op.EQ, possibleKeyValues[i]));
                        }
                        cList.Add(cl);
                    }
                    cmd = cList.getCmd();
                    SubShowTables SST = new SubShowTables(ref dbm, nextTable, cList);
                    SST.Show();
                } else {

                    //Case 2: ID is only one column

                    Condition c = new Condition(nextTableColumns[0].Name, Condition.Op.EQ);
                    foreach(Object[] possibleKeyValues in values) {
                        c.Add(possibleKeyValues[0]);
                    }
                    cmd = c.getCmd();
                    SubShowTables SST = new SubShowTables(ref dbm, nextTable, c);
                    SST.Show();
                }
            }
        }

   

        private void tsTBMax_TextChanged(object sender, EventArgs e) {
            Int16 num;
            if (!Int16.TryParse(tsTBMax.Text, out num) || num < 0) {
                EP.SetError(((ToolStripTextBox) sender).Control, "Benötigt eine Zahl >= 0");
            } else {
                EP.SetError(((ToolStripTextBox)sender).Control, null);
            }
        }

        private void tsCBSchema_SelectedIndexChanged(object sender, EventArgs e) {
            tsCBTable.Items.Clear();
            tsCBTable.Enabled = false;
            tsBtShow.Enabled = false;

            if (tsCBSchema.Text != null) {
                tsCBTable.Enabled = true;
                foreach (String n in dbm.getTableNames(tsCBSchema.Text)) {
                    tsCBTable.Items.Add(n);
                }
                //prepareCommand();
            }
        }

        private void tsCBTable_SelectedIndexChanged(object sender, EventArgs e) {
                tsBtShow.Enabled = tsCBTable.SelectedItem != null;
                

                if (tsBtShow.Enabled) {
                    this.RecentTableInfo = dbm.getTableInfo(tsCBSchema.Text, tsCBTable.Text);
                    cbHideBigBLOBs.Enabled = false;

                    foreach(ColumnInfo ci in this.RecentTableInfo.Values) {
                        if(ci is ColumnInfoImageFile) {
                            cbHideBigBLOBs.Enabled = true;
                            break;
                        }
                    }

                    this.initialCommand = SQLCommandBuilder.CommandBuilder.getSELECT(RecentTableInfo, cbHideBigBLOBs.Enabled && cbHideBigBLOBs.Checked);
                    this.initialCommand.Connection = this.dbm.Connection.Clone();
                    prepareCommand();
                } 
            
                
        }

        
        private void prepareCommand() {
            if(initialCommand != null) {
                this.tableCommand = initialCommand.Clone();
                if(this.RecentTableInfo != null && this.RecentTableInfo.hasPK) {
                    tableCommand.CommandText += String.Format(" ORDER BY \"{0}\" ASC ", this.RecentTableInfo.PrimaryKey[0].Name);
                }
                int limit = Int16.Parse(tsTBMax.Text);
                int offset = Int16.Parse(tsTBOFFSET.Text);
                if(limit > 0) tableCommand.CommandText += " LIMIT " + limit;
                if(offset > 0) tableCommand.CommandText += " OFFSET " + offset;
                tableCommand.Connection = this.dbm.Connection;
                RTB_CMD.Text = SQLCommandBuilder.CommandBuilder.CommandToString(tableCommand);

            }
        }

   
        private void DGV_DataSourceChanged(object sender, EventArgs e) {
            tsbUpdate.Enabled = false;
        }

        private void DGV_DataError(object sender, DataGridViewDataErrorEventArgs e) {
            Exception ex = e.Exception;
            if (ex != null) {
                e.ThrowException = false;
                String msg = e.Exception.Message +
                    "\nBenötigter Wert-Typ: " + 
                    this.DGV.Columns[e.ColumnIndex].ValueType.Name;
                MessageBox.Show(msg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                e.Cancel = true;
            } else { 
               
            }

        }

        /// <summary>
        /// Struct to store information required to run the update background worker.
        /// </summary>
        private struct BGW_Info {
            public NpgsqlConnection connection;
            public NpgsqlCommand update;
            public NpgsqlCommand insert;
            public NpgsqlCommand delete;
        }

        private void tsbUpdate_Click(object sender, EventArgs e) {
            this.Cursor = Cursors.WaitCursor;
            
            try {
                BackgroundWorkerProgressBar BGWPG = new BackgroundWorkerProgressBar();
                BackgroundWorker BGW = BGWPG.BackgroundWorker;
                BGW.RunWorkerCompleted +=new RunWorkerCompletedEventHandler(BGW_RunWorkerCompleted);
                BGW.DoWork +=new DoWorkEventHandler(BGW_DoWork);
                
                BGW_Info info = new BGW_Info();
                info.connection = this.dbm.Connection;
                info.update = CommandBuilder.getUPDATE(this.RecentTableInfo, dt, true);
                info.insert = CommandBuilder.getINSERT(this.RecentTableInfo, dt, true);
                info.delete = CommandBuilder.getDELETE(this.RecentTableInfo, dt, true);

                BGW.RunWorkerAsync(info);


            } catch(Exception ex) {
                MessageBox.Show(TextHelper.Exception2String(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            } finally {
                this.Cursor = Cursors.Default;
            }
        }

        /// <summary>
        /// BackgroundWorker Routine to delete, insert and update a database table.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void BGW_DoWork(object sender, DoWorkEventArgs e) {
            BackgroundWorker bgw = sender as BackgroundWorker;
            BGW_Info info = (BGW_Info)e.Argument;
            NpgsqlConnection con = info.connection.Clone();
            con.Open();
            NpgsqlTransaction trans = con.BeginTransaction();
            bool done = false;
            try {
                if(bgw.CancellationPending) return;
                if(info.delete != null){
                    bgw.ReportProgress(1, "Delete values...");
                    info.delete.Connection = con;
                    info.delete.Transaction = trans;
                    info.delete.ExecuteNonQuery();
                }
                if(bgw.CancellationPending) return;
                if(info.insert != null){
                    bgw.ReportProgress(33, "Insert new values...");
                    info.insert.Connection = con;
                    info.insert.Transaction = trans;
                    info.insert.ExecuteNonQuery();
                }
                if(bgw.CancellationPending) return;
                if(info.update != null){
                    bgw.ReportProgress(33, "Update existing values...");
                    info.update.Connection = con;
                    info.update.Transaction = trans;
                    info.update.ExecuteNonQuery();
                }
                if(bgw.CancellationPending) return;
                bgw.ReportProgress(100, "Done!");
                System.Threading.Thread.Sleep(500);

                done = true;
            } catch(Exception ex) {
                throw ex;

            } finally {
                if(bgw.CancellationPending || !done) {
                    trans.Rollback();
                } else {
                    trans.Commit();
                }
                con.Close();
            }

        }

        void BGW_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
            if(e.Error != null) {
                FormHelper.ShowErrorBox(e.Error);
            } else if(e.Cancelled) {


            } else {
                this.dt.AcceptChanges();
                this.tsbUpdate.Enabled = false;
            }
            
            this.Cursor = Cursors.Default;
        }

        
        private void SubShowTables_FormClosing(object sender, FormClosingEventArgs e) {
            this.DGV.CancelEdit();     
        }

        private void tsbSaveDGV_Click(object sender, EventArgs e) {
            SaveDGV sdgv = new SaveDGV(ref this.DGV, RecentTableInfo.PrimaryKey.Names, RecentTableInfo.Name, "Speichere Tabelle " + RecentTableInfo.Name, false);
            sdgv.ShowDialog();
        }

        private void DGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) {
            DGV.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
            DGV.CurrentRow.Selected = true;
        }
        private void rb_sortCols_CheckedChanged(object sender, EventArgs e) {
            DGV.SelectionMode = DataGridViewSelectionMode.CellSelect;
            foreach (DataGridViewColumn c in DGV.Columns) {
                c.SortMode = DataGridViewColumnSortMode.Automatic;
            }
        }

        private void tsBtRowsBack_Click(object sender, EventArgs e) {
            Int32 max, offset;
            if (Int32.TryParse(tsTBMax.Text, out max) &&
                Int32.TryParse(tsTBOFFSET.Text,out offset)) {
                tsTBOFFSET.Text = Math.Max(0, offset - max).ToString();
                toolStripBtShow_Click(null, null);
            }
            

        }

        private void tsBtRowsMore_Click(object sender, EventArgs e) {
            Int32 max, offset;
            if (Int32.TryParse(tsTBMax.Text,out max) &&
                Int32.TryParse(tsTBOFFSET.Text,out offset)) {
                tsTBOFFSET.Text = (offset + max).ToString();
                toolStripBtShow_Click(null, null);
            }
            
        }

        private void DGV_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e) {
            tsbUpdate.Enabled = true;
            DGV_RowNumberChanges();
            
        }
        private void DGV_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e) {
            tsbUpdate.Enabled = true;
            DGV_RowNumberChanges();
        }

        private void DGV_RowNumberChanges() {
            foreach (DataGridViewRow r in DGV.Rows) {
                //result.HeaderCell.Value = result.Index;
            }
        }

        private void DGV_CellContentClick(object sender, DataGridViewCellEventArgs e) {
           
        }

        private void DGV_CellClick(object sender, DataGridViewCellEventArgs e) {
            if (e.ColumnIndex < 0) {
                DGV.EditMode = DataGridViewEditMode.EditProgrammatically;
            } else {
                DGV.EditMode = DataGridViewEditMode.EditOnEnter;
            }
            
        }

        private void toolStripComboBox1_Click(object sender, EventArgs e) {

        }

           

        /*
        private void DGV_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e) {
            if (rb_selectCols.Checked && DGV.SelectionMode != DataGridViewSelectionMode.RowHeaderSelect) {
                foreach (DataGridViewColumn cDBColumnName in DGV.Columns) {
                    cDBColumnName.SortMode = DataGridViewColumnSortMode.Programmatic;
                }
                
                DGV.SelectionMode = DataGridViewSelectionMode.RowHeaderSelect;
            } else if (rbSortColumns.Checked && DGV.SelectionMode == DataGridViewSelectionMode.RowHeaderSelect) {
                DGV.SelectionMode = DataGridViewSelectionMode.CellSelect;
                foreach (DataGridViewColumn cDBColumnName in DGV.Columns) {
                    cDBColumnName.SortMode = DataGridViewColumnSortMode.Automatic;
                }
            }
            DataGridViewColumn C = DGV.Columns[e.ColumnIndex];
            if (rbSortColumns.Checked) {
                switch (C.HeaderCell.SortGlyphDirection) {
                    case SortOrder.None: 
                        C.HeaderCell.SortGlyphDirection = SortOrder.Ascending;
                        DGV.Sort(C, ListSortDirection.Ascending); 
                        break;
                    case SortOrder.Ascending: DGV.Sort(C, ListSortDirection.Ascending); break;
                    case SortOrder.Descending: DGV.Sort(C, ListSortDirection.Descending); break;
                }
            } else if (rb_selectCols.Checked) {
                //DGV.SelectionMode= DataGridViewSelectionMode. .Selected = true;
            }

        }
        */

    }

}
